<?php

/**
 * Created by Atom.
 * User: qiliping
 * Date: 2017/1/19
 * Time: 14:10
 */
class School_student_model extends CI_Model
{

  public function add_student_course($school_id,$student_id,$course_list)
  {
    foreach ($course_list as $val) {
      // print_r($course_list);exit;
      $cour = array(
        'user_id' => 0,
        'student_id' => $student_id,
        'course_id' => $val,
        'school_id' => $school_id,
        'course_type' => 3,
        'effective_time' => 0,
        'status' => 0,
        'add_time' => time(),
        );
       $this->db->insert('o2o_student_course',$cour);
    }
    return 1;
  }

  /**
   * 获取学校学生
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function get_student_simple_by_school($school_id)
  {
    $sql = "select student_id,name from {$this->db->dbprefix}o2o_school_student  where school_id = {$school_id} ";
    return $this->db->query($sql)->result_array();
  }


  /**
   * 获取学校学员
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function get_student_by_school($limit = 0, $offset = 10, $conditions = array())
  {
    $where = '1=1 ';
    if(!empty($conditions['school_id'])){
      $where .= " AND stu.school_id = '{$conditions['school_id']}' ";
    }
    if(!empty($conditions['name'])){
      $where .= " AND stu.name LIKE '%{$conditions['name']}%' ";
    }
    if(!empty($conditions['mobile'])){
      $where .= " AND stu.mobile = '{$conditions['mobile']}' ";
    }
    if(!empty($conditions['school_campus_id'])){
      $where .= " AND stu.school_campus_id = '{$conditions['school_campus_id']}' ";
    }
    if(!empty($conditions['st'])){
      $conditions['st'] = strtotime($conditions['st']);
      $where .= " AND stu.add_time > {$conditions['st']} ";
    }
    if(!empty($conditions['et'])){
      $conditions['et'] = strtotime($conditions['et']) + 86400;
      $where .= " AND stu.add_time < {$conditions['et']} ";
    }
    $sql = "select SQL_CALC_FOUND_ROWS stu.*,campus.name as campus_name,(select count(0) from {$this->db->dbprefix}o2o_student_course sc where sc.school_id = '{$conditions['school_id']}' and student_id = stu.student_id) as kemu ,(select count(0) from {$this->db->dbprefix}o2o_student_course sc where sc.school_id = '{$conditions['school_id']}' and sc.student_id = stu.student_id and sc.status=0) as kemu_not from {$this->db->dbprefix}o2o_school_student stu
      left join {$this->db->dbprefix}o2o_school_campus campus on campus.id = stu.school_campus_id
       where $where order by stu.student_id desc LIMIT $limit, $offset";
    $result = $this->db->query($sql)->result_array();
    $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
    return array("count" => $row_num['row_num'], "result" => $result);
  }

  /**
   * 获取待分班的学员信息
   * @param  [type] $school_id [description]
   * @return [type]            [description]
   */
  public function get_undistributed_course_student($limit = 0, $offset = 10, $conditions = array())
  {
    $where = 'stu_cou.course_type =3 and stu_cou.status=0 AND campus.id > 0 AND cour.id > 0 AND stu.student_id > 0';
    if(!empty($conditions['school_id'])){
      $where .= " AND stu.school_id = '{$conditions['school_id']}' ";
    }
    if(!empty($conditions['name'])){
      $where .= " AND stu.name LIKE '%{$conditions['name']}%' ";
    }
    if(!empty($conditions['school_campus_id'])){
      $where .= " AND stu.school_campus_id = '{$conditions['school_campus_id']}' ";
    }
    if(!empty($conditions['st'])){
      $conditions['st'] = strtotime($conditions['st']);
      $where .= " AND stu_cou.add_time > {$conditions['st']} ";
    }
    if(!empty($conditions['et'])){
      $conditions['et'] = strtotime($conditions['et']) + 86400;
      $where .= " AND stu_cou.add_time < {$conditions['et']} ";
    }
    if(!empty($conditions['mobile'])){
      $where .= " AND stu.mobile = {$conditions['mobile']} ";
    }
    if(!empty($conditions['mobile'])){
      $where .= " AND stu.mobile = {$conditions['mobile']} ";
    }
    if(!empty($conditions['course_id'])){
      $where .= " AND cour.id = {$conditions['course_id']} ";
    }
    $sql = "select SQL_CALC_FOUND_ROWS stu_cou.id,stu_cou.order_num,stu_cou.add_time as order_time,stu_cou.course_id,cour.title,stu.*,campus.name as campus_name from {$this->db->dbprefix}o2o_student_course stu_cou ".
           " left join {$this->db->dbprefix}o2o_course cour on cour.`id` = stu_cou.course_id ".
           " left join {$this->db->dbprefix}o2o_school_student stu on stu.student_id = stu_cou.student_id ".
           " left join {$this->db->dbprefix}o2o_school_campus campus on campus.id = stu.school_campus_id ".
           "  where $where ";
    $result = $this->db->query($sql)->result_array();
    $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
    return array("count" => $row_num['row_num'], "result" => $result);
  }

  /**
   * 获取班级学员
   * @param  [type] $class_id [description]
   * @return [type]            [description]
   */
  public function get_student_by_class($class_id)
  {
    $sql = "select cstu.*,stu.*,user.head_image_url from {$this->db->dbprefix}o2o_class_student cstu ".
    " left join {$this->db->dbprefix}o2o_student stu on stu.student_id = cstu.student_id ".
    " left join {$this->db->dbprefix}user user on user.id = stu.user_id ".
    " where cstu.class_id = {$class_id} order by cstu.id desc";

    return $this->db->query($sql)->result_array();
  }

  function delete_class_student($id){
    $this->db->trans_start();

    //0查询班级课程信息
    $sql = "select cl.course_id,cs.student_id from {$this->db->dbprefix}o2o_class_student cs left join {$this->db->dbprefix}o2o_class cl on cl.id=cs.class_id where cs.id=$id limit 1";
    $result = $this->db->query($sql)->row_array();

    //1删除班级学员
    $this->db->delete('o2o_class_student',array('id' => $id ));
    //2更改课程状态
    if($result){
      $this->db->update('o2o_student_course', array('status' => 0 ), array('student_id' => $result['student_id'],'course_id'=> $result['course_id']));
    }
    $this->db->trans_complete();
  }

  /**
   * 获取未分配的学员（根据学校id和线下课程id）
   * @param  [type] $school_id [description]
   * @param  [type] $course_id [description]
   * @return [type]            [description]
   */
  public function get_undistributed_student($school_id,$course_id)
  {
    # code...
    $sql = "SELECT stu.* FROM {$this->db->dbprefix}o2o_student_course stu_cou ".
           " left join {$this->db->dbprefix}o2o_student stu on stu.student_id = stu_cou.student_id ".
           " WHERE stu_cou.course_type=3 AND stu_cou.status=0 AND stu_cou.course_id ={$course_id} AND stu_cou.school_id={$school_id}";
     return $this->db->query($sql)->result_array();
  }

  /**
   * 分配学员
   * @param  [type] $student_id_array [description]
   * @param  [type] $class_id         [description]
   * @return [type]                   [description]
   */
  public function student_allot_class($student_id_array,$class_id)
  {
    $this->db->trans_start();

    foreach ($student_id_array as $stu_id) {
      //插入班级学员数据o2o_class_student
      $class_stu = array(
        'class_id' => $class_id,
        'student_id' => $stu_id,
        'sort' => 0,
        'status' => 1,
        'add_time' => time(),
     );
     $this->db->insert('o2o_class_student',$class_stu);

     //更新edu_o2o_student_course
     $sql = "update {$this->db->dbprefix}o2o_student_course set status=1 where student_id = {$stu_id} AND course_id = (select course_id from {$this->db->dbprefix}o2o_class where id={$class_id}) AND status =0";

     $this->db->query($sql);
    }
    $this->db->trans_complete();
    return 1;
  }

  function get_student_info_by_uid($uid, $school_id){
    $sql = "SELECT s.* FROM {$this->db->dbprefix}o2o_school_student AS ss LEFT JOIN {$this->db->dbprefix}o2o_student AS s ON(s.student_id=ss.student_id) WHERE ss.user_id = '{$uid}' AND ss.school_id = '{$school_id}'";
    return $this->db->query($sql)->row_array();
  }

}
